package com.ets.User_DAO;

//import User;

//import User;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.ets.common.DateUtils;
import com.ets.database.DB_Pipe;

/******************************************************************************* 
 The <big><b>UserDB</b></big> class implements the reader & writer methods of
 the <b>UserDAO</b> interface to provide the data access to the
 <big><b>User</b></big> table
 <br><br>
 @author D.Merkling - 1:33:38 PM - Aug 22, 2011
 <br><br>
 @version 1.2  
*******************************************************************************/ 
public class UserDB extends DB_Pipe implements UserDAO
  {
	private Connection connection = null;
	
   public UserDB()
	 {
	   this.connect();
	 }
/*
 * PGR1RIF Added Connect()
 */
   
	private void connect()
    {
	  //Connection con = null;
	   //Class.forName("com.mysql.jdbc.Driver"); 
	  try
	    {
  	     Class.forName("com.mysql.jdbc.Driver");
		 //String url      = "jdbc:mysql://localhost:3306/MYDB";
  	   String url      = "jdbc:mysql://localhost:3306/e_t_s";
  	  	 //String username = "root";
		 String username = "ETS_Mstr";
		 //String password = "rootpwd";
		 String password = "ETS_Pswd";
		 connection      = DriverManager.getConnection(url, username, password);
		 System.out.println ("Database connection established");

	    }
	  catch(ClassNotFoundException e)
	    {
		 System.err.println("Database driver was not found.");
	    }
	  catch(SQLException e)
	    {
		 System.err.println("Error connecting to the database: " + e);
	    }
	 // return; 
    }   
 
/*
 * End PGR1RIF added Connect()	
 */
	
/*
 * Start PGR1RIF added disconnect (Connection con) * 	
 */
	   private void disconnect (Connection con)
	     {
		  try
		    {
			 con.close(); 
		    }
		  catch(SQLException e)
		    {
			 System.err.println("Error disconnecting from the database: " + e);
		    }
	     }	
/*
 * End PGR1RIF added disconnect (Connection con) * 	
*/ 
	   

   /*******************************************************************************
	User reader methods
   *******************************************************************************/
   
   public boolean existsUser (String User)
	 {
	  String tmpUser = User.trim();
	         tmpUser = tmpUser.toUpperCase();
	  String exists = "SELECT User "      +
			            "FROM user "      +
			           "WHERE User  = ?";
	   try
		{
	     DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(exists);
		 ps.setString(1, tmpUser);
		 ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
		    return true;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
		    return false;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
	    {
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public User getUser (String User)
	 {
	  String sel = "SELECT User, "   +
                          "Nm_Pfx, " +
                          "Nm_Fst, " +
                          "Nm_Mid, " +
                          "Nm_Lst, " +
                          "Nm_Sfx, " +
                          "Addr, "   +
                          "Phn, "    +
	                      "Phn2, "   +
	                      "Email, "  +
	                      "Email2 "  +
	                 "FROM user "    +
	                "WHERE User = ?";
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(sel);
		 ps.setString(1, User);
		 ResultSet         rs  = ps.executeQuery();
		 if (rs.next())
		   {
		    String Nm_Pfx = rs.getString("Nm_Pfx");
		    String Nm_Fst = rs.getString("Nm_Fst");
		    String Nm_Mid = rs.getString("Nm_Mid");
		    String Nm_Lst = rs.getString("Nm_Lst");
		    String Nm_Sfx = rs.getString("Nm_Sfx");
		    int    Addr   = rs.getInt("Addr");
		    String Phn1   = rs.getString("Phn");
		    String Phn2   = rs.getString("Phn2");
		    String Email  = rs.getString("Email");
		    String Email2 = rs.getString("Email2");
		    User u        = new User(User, "pswd", Nm_Pfx, Nm_Fst, Nm_Mid, Nm_Lst, Nm_Sfx, Addr, Phn1, Phn2, Email, Email2);
		    rs.close();
		    ps.close();
		    return u;
		   }
		 else
		   return null;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	  finally
	    {
		 DB_Disconnect(DB_Pipe);
		}
	 }
   
   public boolean isValidPswd (String User, String Pswd)
	 {
	  String tmpUser = User.trim();
	         tmpUser = tmpUser.toUpperCase();
	  String tmpPswd = Pswd.trim();
	  // Put in call to encryption method here, so we match what's in the database
	  String exists = "SELECT User, "    +
	                         "Pswd "     +
			            "FROM user "     +
			           "WHERE User = ? " +
			             "AND Pswd = ?";
	   try
		{
	     DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(exists);
		 ps.setString(1, tmpUser);
		 ps.setString(2, tmpPswd);
		 ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
		    return true;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
		    return false;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
	    {
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public boolean getUsers (ArrayList<User> users)
	 {
	  String qry = "SELECT User, "      +
                          "Nm_Pfx, "    +
                          "Nm_Fst, "    +
                          "Nm_Mid, "    +
                          "Nm_Lst, "    +
                          "Nm_Sfx, "    +
                          "Addr, "      +
                          "Phn, "       +
                          "Phn2, "      +
                          "Email, "     +
                          "Email2 "     +
 	                 "FROM user "       +
	                "ORDER BY User ASC";
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(qry);
		 ResultSet         rs = ps.executeQuery();
		 while(rs.next())
		   {
		    String  User   = rs.getString("User");
		    String  Nm_Pfx = rs.getString("Nm_Pfx");
		    String  Nm_Fst = rs.getString("Nm_Fst");
		    String  Nm_Mid = rs.getString("Nm_Mid");
		    String  Nm_Lst = rs.getString("Nm_Lst");
		    String  Nm_Sfx = rs.getString("Nm_Sfx");
		    int     Addr   = rs.getInt("Addr");
		    String  Phn1   = rs.getString("Phn");
		    String  Phn2   = rs.getString("Phn2");
		    String  Email  = rs.getString("Email");
		    String  Email2 = rs.getString("Email2");
		    User    u      = new User(User, "pswd", Nm_Pfx, Nm_Fst, Nm_Mid, Nm_Lst, Nm_Sfx, Addr, Phn1, Phn2, Email, Email2);
		    users.add(u);
		   }
		 rs.close();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return false;
		}
	  finally
	    {
		 DB_Disconnect(DB_Pipe);
		}
	 }

   /*******************************************************************************
	User writer methods
   *******************************************************************************/
   
   public boolean addUser (User u)
	 {
	  String ins = "INSERT "                                              +
	                 "INTO user (User, "                                  +
                                "Pswd, "                                  +
	                            "Nm_Pfx, "                                +
	                            "Nm_Fst, "                                +
	                            "Nm_Mid, "                                +
	                            "Nm_Lst, "                                +
	                            "Nm_Sfx, "                                +  
	                            "Addr, "                                  +
	                            "Phn, "                                   +
	                            "Phn2, "                                  +
	                            "Email, "                                 +
	                            "Email2, "                                +
	                            "TMS_Crtd, "                              +
	                            "User_Crtd, "                             +
	                            "TMS_Updt, "                              +
	                            "User_Updt) "                             +
			       "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(ins);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setString(1, u.getUser());
		 String tmpPswd = u.getPswd();
		 // Put in call to encryption method here, before loading into the database
		 ps.setString(2, tmpPswd);
		 ps.setString(3, u.getNmPfx());
		 ps.setString(4, u.getNmFst());
		 ps.setString(5, u.getNmMid());
		 ps.setString(6, u.getNmLst());
		 ps.setString(7, u.getNmSfx());
		 ps.setInt(8, u.getAddr());
		 ps.setString(9, u.getPhn1());
		 ps.setString(10, u.getPhn2());
		 ps.setString(11, u.getEmail());
		 ps.setString(12, u.getEmail2());
		 ps.setTimestamp(13, ts);
		 ps.setString(14, "USER0001");
		 ps.setTimestamp(15, ts);
		 ps.setString(16, "USER9999");
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
	    {
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public boolean updateUser (User u)
	 {
	  String upd = "UPDATE user "           +
                      "SET Nm_Pfx    = ?, " +
                          "Nm_Fst    = ?, " +
                          "Nm_Mid    = ?, " +
                          "Nm_Lst    = ?, " +
                          "Nm_Sfx    = ?, " +
                          "Addr      = ?, " +
                          "Phn       = ?, " +
                          "Phn2      = ?, " +
                          "Email     = ?, " +
                          "Email2    = ?, " +
                          "TMS_Updt  = ?, " +
			              "User_Updt = ? "  +
		            "WHERE User = ?";
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(upd);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setString(1, u.getNmPfx());
		 ps.setString(2, u.getNmFst());
		 ps.setString(3, u.getNmMid());
		 ps.setString(4, u.getNmLst());
		 ps.setString(5, u.getNmSfx());
		 ps.setInt(6, u.getAddr());
		 ps.setString(7, u.getPhn1());
		 ps.setString(8, u.getPhn2());
		 ps.setString(9, u.getEmail());
		 ps.setString(10, u.getEmail2());
		 ps.setTimestamp(11, ts);
		 ps.setString(12, "USER8888");
		 ps.setString(13, u.getUser());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	  finally
	    {
		 DB_Disconnect(DB_Pipe);
		}
	 }
   
   public boolean deleteUser (User u)
	 {
	  String del = "DELETE "         +
	                 "FROM user "    +
			        "WHERE User = ?";
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(del);
		 ps.setString(1, u.getUser());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
	    {
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public boolean updatePswd (String User, String Old_Pswd, String New_Pswd)
	 {
	  String tmpUser = User.trim();
	         tmpUser = tmpUser.toUpperCase();
	  String tmpOld  = Old_Pswd.trim();
	  // Put in call to encryption method here, so we match what's in the database
	  String tmpNew  = New_Pswd.trim();
	  // Put in call to encryption method here, before updating the database
	  String upd = "UPDATE user "           +
                      "SET Pswd      = ?, " +
                          "TMS_Updt  = ?, " +
			              "User_Updt = ? "  +
		            "WHERE User = ? "       +
			          "AND Pswd = ?";
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(upd);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setString(1, tmpNew);
		 ps.setTimestamp(2, ts);
		 ps.setString(3, "USER8888");
		 ps.setString(4, tmpUser);
		 ps.setString(5, tmpOld);
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	  finally
	    {
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public ArrayList<User> getUsers()
	 {
	  try
		{
		 ArrayList<User> users = new ArrayList<User>();

		 String query = "SELECT User, "      +
		 					   "Pswd, "      + 	
		                       "Nm_Pfx, "    +
		                       "Nm_Fst, "    +
		                       "Nm_Mid, "    +
		                       "Nm_Lst, "    +
		                       "Nm_Sfx, "    +
		                       "Addr, "       +
		                       "Phn, "       +
		                       "Phn2, "       +
		                       "Email, "      +
		                       "Email2 "     +
			 			  "FROM user "       +
					     "ORDER BY User ASC";
		 System.out.println(query);
		 PreparedStatement ps = null;
		 ps = connection.prepareStatement(query);
		 ResultSet         rs = ps.executeQuery();

		 while(rs.next())
		   {
		    String  User   = rs.getString("User");
		    String  Pswd   = rs.getString("Pswd");
		    String  Nm_Pfx = rs.getString("Nm_Pfx");
		    String  Nm_Fst = rs.getString("Nm_Fst");
		    String  Nm_Mdl = rs.getString("Nm_Mid");
		    String  Nm_Lst = rs.getString("Nm_Lst");
		    String  Nm_Sfx = rs.getString("Nm_Sfx");
		    int 	Addr   = rs.getInt("Addr");	
		    String  Phn1   = rs.getString("Phn");
		    String  Phn2   = rs.getString("Phn2");
		    String  Email   = rs.getString("Email");
		    String  Email2   = rs.getString("Email2");
		    System.out.println(Nm_Fst);
		    User u         = new User(User, Pswd, Nm_Pfx, Nm_Fst, Nm_Mdl, Nm_Lst, Nm_Sfx, Addr, Phn1, Phn2, Email, Email2);
		    users.add(u);
		   }
		 rs.close();
		 ps.close();
		 disconnect(connection);
		 return users;
		}
	  catch(SQLException sqle)
		{
		 sqle.printStackTrace();  // for debugging
		 return null;
		}
	 }
}

//  }